﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

/// <summary>
/// Summary description for DBUtil
/// </summary>
public static class DBUtil
{

    public static int ExecuteNonQuery(string SQL)
    {
        int i = 0;
        MySqlConnection con = Util.con();
        MySqlCommand cmd = new MySqlCommand(SQL, con);
        cmd.CommandTimeout = 0;
        con.Open();
        using (MySqlTransaction trans = con.BeginTransaction())
        {
            try
            {
                i = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();

                con.Dispose();
                cmd.Dispose();
            }
        }
        return i;
    }

    public static int ExecuteNonQuery(MySqlCommand cmd)
    {
        int i = 0;
        MySqlConnection con = Util.con();
        cmd.Connection = con;
        con.Open();
        using (MySqlTransaction trans = con.BeginTransaction())
        {
            try
            {
                cmd.CommandTimeout = 0;
                cmd.Transaction = trans;
                i = cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();

                con.Dispose();
            }
        }
        return i;
    }

    public static bool ExecuteNonQuery(List<MySqlCommand> list)
    {
        bool ret = false;
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandTimeout = 0;
        MySqlConnection con = Util.con();
        cmd.Connection = con;
        con.Open();
        using (MySqlTransaction trans = con.BeginTransaction())
        {
            try
            {
                foreach (MySqlCommand item in list)
                {
                    cmd = item.Clone();
                    cmd.Transaction = trans;
                    cmd.Connection = con;
                    cmd.ExecuteNonQuery();
                }
                trans.Commit();
                ret = true;
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();

                con.Dispose();

                for (int j = list.Count - 1; j >= 0 ; j--)
                {
                    list[j].Dispose();
                }
            }
        }

        return ret;
    }

    public static DataTable ExecuteDataTable(string SQL)
    {
        MySqlConnection con = Util.con();
        try
        {
            DataTable dt = new DataTable();
            MySqlCommand cmd = new MySqlCommand(SQL, con);
            cmd.CommandTimeout = 0;
            MySqlDataAdapter DA = new MySqlDataAdapter(cmd);
            DA.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
                con.Close();

            con.Dispose();
        }
    }

    public static DataTable ExecuteDataTable(MySqlCommand cmd)
    {
        MySqlConnection con = Util.con();
        try
        {
            cmd.CommandTimeout = 0;
            DataTable dt = new DataTable();
            cmd.Connection = con;
            if (con.State == ConnectionState.Open)
                con.Close();

            MySqlDataAdapter DA = new MySqlDataAdapter(cmd);
            DA.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
                con.Close();

            con.Dispose();
        }
    }

    public static object ExecuteScalar(MySqlCommand cmd)
    {
        MySqlConnection con = Util.con();
        try
        {
            cmd.CommandTimeout = 0;
            cmd.Connection = con;
            if (con.State == ConnectionState.Closed)
                con.Open();

            return cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
                con.Close();

            con.Dispose();
        }
    }

}